--SELECT * FROM FN_LOAD_MODIFIED_QUOTATION_ORDER_LINEITEMS( 26, 1 ) ORDER BY LINEITEM_ID, CHANGE_NUMBER
ALTER FUNCTION FN_LOAD_MODIFIED_QUOTATION_ORDER_LINEITEMS
(
	@QUOTATION_ID INT,
	@CONSIDER_TRANSFERRED_QUOTES BIT
)
RETURNS @FN_RESULTS TABLE
( 
	QUOTATION_ID 		INT, 
	QUOTATION_NO 		VARCHAR(50), 
	LINEITEM_ID 		INT, 
	LINEITEM_NO 		VARCHAR(50), 
	PO_LINEITEM_NO 		INT, 
	CHANGE_NUMBER 		INT,
	PRODUCT_ID 		INT, 
	PART_NUMBER 		VARCHAR(50), 
	CUSTOMER_PART_NO 	VARCHAR(50), 
	REQUESTED_DATE 		DATETIME, 
	BOOKING_DATE 		DATETIME, 
	QUANTITY 		INT, 
	UNIT_PRICE 		FLOAT, 
	AMOUNT 			FLOAT, 
	TAXABLE 		INT, 
	TRANSFER 		BIT, 
	IS_APPROVED 		BIT, 
	SALESORDER_NO 		VARCHAR(50), 
	SO_LINEITEM_NO 		INT, 
	LEAD_TIME  		VARCHAR(50),
	REQUESTED_PRICE 	FLOAT, 
	CUST_RESALE_PRICE 	FLOAT, 
	SUGG_RESALE_PRICE 	FLOAT,
	MIN_ORDER_QTY		INT
)
AS
BEGIN
	DECLARE @QUOTATION_NO VARCHAR(50)

	SELECT @QUOTATION_NO = QUOTATION_NO FROM QUOTATION_TABLE WHERE QUOTATION_ID = @QUOTATION_ID

	IF( @CONSIDER_TRANSFERRED_QUOTES = 1 )
	BEGIN
		INSERT INTO @FN_RESULTS 
		SELECT @QUOTATION_ID, @QUOTATION_NO, LINEITEM_ID, LINE_ITEM_NO, PO_LINE_ITEM_NO, CHANGE_NUMBER, P.PRODUCT_ID, PART_NUMBER, NULL, REQUESTED_DATE, BOOKING_DATE, QTY, UNIT_COST, AMOUNT, TAXABLE, TRANSFER, QCOLI.IS_APPROVED, NULL, NULL, LEAD_TIME, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE, MIN_ORDER_QTY 
		FROM QUOTATION_CHANGE_ORDER_LINEITEM QCOLI, PRODUCT P 
		WHERE 
			QCOLI.PRODUCT_ID = P.PRODUCT_ID AND 
			SO_LINEITEM_ID IS NULL AND 
			CHANGE_NUMBER <> ( SELECT ISNULL( MAX(CHANGE_NUMBER), 0 ) FROM QUOTATION_CHANGE_ORDER_LINEITEM SUB_TABLE WHERE SUB_TABLE.LINEITEM_ID = QCOLI.LINEITEM_ID AND SUB_TABLE.SO_LINEITEM_ID IS NULL ) AND
			QUOTATION_ID = @QUOTATION_ID	
	END
	ELSE
	BEGIN
		INSERT INTO @FN_RESULTS 
		SELECT @QUOTATION_ID, @QUOTATION_NO, QCOLI.LINEITEM_ID, QCOLI.LINE_ITEM_NO, PO_LINE_ITEM_NO, CHANGE_NUMBER, P.PRODUCT_ID, PART_NUMBER, NULL, REQUESTED_DATE, BOOKING_DATE, QTY, UNIT_COST, AMOUNT, TAXABLE, TRANSFER, QCOLI.IS_APPROVED, SALESORDER_NO, COOL_LINE_ITEM_NO, LEAD_TIME, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE, MIN_ORDER_QTY  
		FROM QUOTATION_CHANGE_ORDER_LINEITEM QCOLI, PRODUCT P, CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO
		WHERE 
			QCOLI.PRODUCT_ID = P.PRODUCT_ID AND 
			QCOLI.SO_LINEITEM_ID = COOL.LINEITEM_ID AND
			COOL.SALESORDER_ID = SO.SALESORDER_ID AND
			SO_LINEITEM_ID IS NOT NULL AND 
			QCOLI.QUOTATION_ID = @QUOTATION_ID	
	END

	RETURN
END

